package dao;

import entity.ProductBean;
import util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDAO {
//    /**
//     * 查询满足条件的任务列表
//     * @param productCode 产品身份码
//     * @param productName 产品名称
//     * @param workshop 加工车间
//     * @param worker 加工人员
//     * @param batch 批次
//     * @return List<productBean>--符合条件的任务列表
//     */
    private String productCode = "";
    private String productName = "";
    private String batch = "";
    private String worker = "";
    private String processingDate = "";
    private String workshop = "";
    private String productSize = "";
    private String processingMethod = "";

    public ProductDAO() {
    }

    public ProductDAO(String productCode, String productName, String batch, String worker, String processingDate, String workshop, String productSize, String processingMethod) {
        this.productCode = productCode;
        this.productName = productName;
        this.batch = batch;
        this.worker = worker;
        this.processingDate = processingDate;
        this.workshop = workshop;
        this.productSize = productSize;
        this.processingMethod = processingMethod;
    }

    public List<ProductBean> findProduct(int curpage) {
        List<ProductBean> productList = new ArrayList<ProductBean>();
        String sql = "SELECT * FROM product where 1=1";
        if (!productCode.isEmpty()) {
            sql += " AND productCode LIKE '%" + productCode + "%'";
        }
        if (!productName.isEmpty()) {
            sql += " AND productName LIKE '%" + productName + "%'";
        }
        if (!workshop.isEmpty()) {
            sql += " AND workshop LIKE '%" + workshop + "%'";
        }
        if (!worker.isEmpty()) {
            sql += " AND worker LIKE '%" + worker + "%'";
        }
        if (!batch.isEmpty()) {
            sql += " AND batch = '" + batch + "'";
        }

        int page_size = 6;
        int start = curpage * page_size - page_size;
        String pageSql = " LIMIT " + start + ", " + page_size;
        sql += pageSql;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            // 访问数据
            while (rs.next()) {
                ProductBean obj = new ProductBean();
                obj.setId(rs.getInt("id"));
                obj.setProductCode(rs.getString("productCode"));
                obj.setProductName(rs.getString("productName"));
                obj.setBatch(rs.getString("batch"));
                obj.setWorker(rs.getString("worker"));
                obj.setProcessingDate(rs.getString("processingDate"));
                obj.setWorkshop(rs.getString("workShop"));
                obj.setProductSize(rs.getString("productSize"));
                obj.setProcessingMethod(rs.getString("processingMethod"));
                productList.add(obj);
            }

        }catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return productList;
    }

    public ProductBean editProduct(int id){
        String sql = "select * from product where id=" + id;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ProductBean obj = new ProductBean();
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.next();
            obj.setId(rs.getInt("id"));
            obj.setProductCode(rs.getString("productCode"));
            obj.setProductName(rs.getString("productName"));
            obj.setBatch(rs.getString("batch"));
            obj.setWorker(rs.getString("worker"));
            obj.setProcessingDate(rs.getString("processingDate"));
            obj.setWorkshop(rs.getString("workShop"));
            obj.setProductSize(rs.getString("productSize"));
            obj.setProcessingMethod(rs.getString("processingMethod"));
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return obj;
    }

    public void editDoProduct(int id){
        String sql = "UPDATE product SET productCode = ?, productName = ?, productSize = ?, batch = ?, worker = ?, processingMethod = ?, processingDate = ?, workshop = ? WHERE id = "+id;
        Connection conn = null;
        PreparedStatement preparedStatement = null;

        try {
            conn = DBUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, productCode);
            preparedStatement.setString(2, productName);
            preparedStatement.setString(3, productSize);
            preparedStatement.setString(4, batch);
            preparedStatement.setString(5, worker);
            preparedStatement.setString(6, processingMethod);
            preparedStatement.setString(7, processingDate);
            preparedStatement.setString(8, workshop);
            int rowsUpdated = preparedStatement.executeUpdate();
            if (rowsUpdated > 0) {

            } else {

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
    }

    public void addDoProduct() {
        String sql = "insert into product(productCode, productName, productSize, batch, worker, processingMethod, processingDate, workshop) values(?,?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement preparedStatement = null;

        try {
            conn = DBUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, productCode);
            preparedStatement.setString(2, productName);
            preparedStatement.setString(3, productSize);
            preparedStatement.setString(4, batch);
            preparedStatement.setString(5, worker);
            preparedStatement.setString(6, processingMethod);
            preparedStatement.setString(7, processingDate);
            preparedStatement.setString(8, workshop);
            int rowsUpdated = preparedStatement.executeUpdate();
            if (rowsUpdated > 0) {

            } else {

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
    }

    public int deleteProduct(int id){
        String sql = "delete from product where id=" + id;
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        int res = -1;
        try {
            conn = DBUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
            res = preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(conn);
        }
        return res;
    }

    public int findPageCount(){
        String countSql="select count(*) from product where 1=1 ";
        String sql="";
        if(productCode != "") {
            sql += "and productCode like '%" + productCode + "%' ";
        }
        if (productName != "") {
            sql += "and productName like '%" + productName + "%' ";
        }
        if (workshop != "") {
            sql += "and workshop like '%" + workshop + "%' ";
        }
        if (worker != "") {
            sql += "and worker like '%" + worker + "%' ";
        }
        if (batch != "") {
            sql += "and batch = '" + batch + "' ";
        }
        countSql+=sql;

        int totalCount = 0;
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet resultSet=null;
        try{
            connection = DBUtil.getConnection();
            pstmt = connection.prepareStatement(countSql);
            resultSet = pstmt.executeQuery();
            if(resultSet.next()){
                totalCount = resultSet.getInt(1);
            }
        }catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            DBUtil.freeConnection(connection);
        }
        int page_size = 6;
        return ((totalCount+page_size)-1)/page_size;
    }
}

